package com.zhazha.stand.util;

import java.io.File;
import java.util.List;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import com.zhazha.stand.entity.User;
import com.zhazha.stand.entity.Department;
import com.zhazha.stand.entity.Problem;

public class ExcelUtil {
	
	private Workbook book;
	
	public List<User> getUserListFromExcel(String fileName, List<Department> dmList) {
		List<User> userList = null;
		try   {
            book  =  Workbook.getWorkbook( new  File(fileName));
            //System.out.println(book.getNumberOfSheets());
            userList = new ArrayList<User>();
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
            	Sheet sheet  =  book.getSheet( i );
            	//System.out.println(sheet.getRows());
            	for (int j = 0; j < sheet.getRows(); j++)
            	{
            		Cell name = sheet.getCell(0, j);
            		Cell department = sheet.getCell(1, j);
            		int grd = 0;
            		if ( "是" == sheet.getCell(2, j).getContents().trim())
            			grd = 1;
            		for(int k = 0; k < dmList.size(); k++)
            		{
            			if (dmList.get(k).getName() == department.getContents().trim())
            				userList.add(new User(0, name.getContents().trim(), dmList.get(k).getId(), grd));
            		}
            		//System.out.println(cell1.getContents());
            		//System.out.println(cell2.getContents());	
            	}
            }
            book.close();
        }   catch  (Exception e)  {
            System.out.println(e);
            return null;
        } 
		return userList;
	}
	
	public List<Department> getDepartmentListFromExcel(String fileName) {
		List<Department> dmList = null;
		try   {
            book  =  Workbook.getWorkbook( new  File(fileName));
            //System.out.println(book.getNumberOfSheets());
            dmList = new ArrayList<Department>();
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
            	Sheet sheet  =  book.getSheet( i );
            	//System.out.println(sheet.getRows());
            	for (int j = 0; j < sheet.getRows(); j++)
            	{
            		Cell name = sheet.getCell(0, j);
            		for (int k = 0; k < dmList.size(); k++)
            			if (dmList.get(k).getName() == name.getContents().trim())
            				continue;
            		dmList.add(new Department(0, name.getContents().trim()));
            		//System.out.println(cell1.getContents());	
            	}
            }
            book.close();
        }   catch  (Exception e)  {
            System.out.println(e);
            return null;
        } 
		return dmList;
	}
	
	public List<Problem> getProblemListFromExcel(String fileName) {
		List<Problem> problemList = null;
		try   {
            book  =  Workbook.getWorkbook( new  File(fileName));
            //System.out.println(book.getNumberOfSheets());
            problemList = new ArrayList<Problem>();
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
            	Sheet sheet  =  book.getSheet( i );
            	//System.out.println(sheet.getRows());
            	for (int j = 0; j < sheet.getRows(); j++)
            	{
            		Problem problem = new Problem();
            		problem.setId(0);
            		problem.setLevel(Integer.parseInt(sheet.getCell(0, j).getContents().trim()));
            		problem.setDescription(sheet.getCell(1, j).getContents().trim());
            		problem.setAnswer(sheet.getCell(2, j).getContents().trim());
            		problem.setChoice1(sheet.getCell(3, j).getContents().trim());
            		problem.setChoice2(sheet.getCell(4, j).getContents().trim());
            		problem.setChoice3(sheet.getCell(5, j).getContents().trim());
            		problem.setChoice4(sheet.getCell(6, j).getContents().trim());
            		//System.out.println(cell1.getContents());	
            		problemList.add(problem);
            	}
            }
            book.close();
        }   catch  (Exception e)  {
            System.out.println(e);
            return null;
        } 
		return problemList;
	}

}
